package com.otc.dbhandler;

import java.sql.*;
import java.util.ArrayList;

import com.otc.model.*;

public class DBHandler {

	private Connection con;
	private Statement stmt;

	public void getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/opinionthatcounts";
			con = DriverManager.getConnection(url, "root", "root");
			stmt = con.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void closeConnection() {
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public ArrayList<String> retrieveUserName(String query) {
		ArrayList<String> al = new ArrayList<String>();
		
		try {
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				al.add(rs.getString(1));
				
			}
		}

		catch (SQLException e) {
			e.printStackTrace();
		}
		return al;
	}
	
	

	public ArrayList<Movies> retrieveMovieDetails(String query) {

		ArrayList<Movies> al = new ArrayList<Movies>();
		try {
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				Movies movie = new Movies(null, null, null, null, null, null,
						null, null, null,null);
				movie.setMovieId(rs.getString(1));
				movie.setMovieDesc(rs.getString(5));
				movie.setMovieDislikes(rs.getString(7));
				movie.setMovieImage(rs.getString(3));
				movie.setMovieLikes(rs.getString(6));
				movie.setMoviename(rs.getString(2));
				movie.setMoviePostBy(rs.getString(4));
				movie.setMovieRating(rs.getString(8));
				movie.setMovieSubmitDate(rs.getString(9));
				movie.setMovieUserId(rs.getString(10));
				al.add(movie);
			}
		}

		catch (SQLException e) {
			e.printStackTrace();
		}
		return al;
	}
	
	public ArrayList<String> retrieveMovieDataForComments(String query) {

		ArrayList<String> al = new ArrayList<String>();
		try {
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {				
				al.add(rs.getString(1));
				al.add(rs.getString(2));
				al.add(rs.getString(3));
				al.add(rs.getString(4));
				al.add(rs.getString(5));				
				al.add(rs.getString(6));
				al.add(rs.getString(7));
				al.add(rs.getString(8));			
			}
		}

		catch (SQLException e) {
			e.printStackTrace();
		}
		return al;
	}
	
	public ArrayList<Comments> retrieveComments(String query) {
		
			ArrayList<Comments> al = new ArrayList<Comments>();
			try {
				ResultSet rs = stmt.executeQuery(query);
				while (rs.next()) {
					
					Comments comment = new Comments(null, null, null, null,null);
					comment.setCommentDescription(rs.getString(4));
					comment.setCommentEmailId(rs.getString(5));	
					comment.setCommentMovieId(rs.getString(3));	
					comment.setCommentName(rs.getString(2));
					comment.setCommentDate(rs.getString(6));
					al.add(comment);
				}
			}

			catch (SQLException e) {
				e.printStackTrace();
			}
			return al;
		}

	public int executeDML(String query) {
		int res = 0;
		try {
			res = stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public String executeSelect(String query) {
		ResultSet rs = null;
		String res = null;
		try {
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			while (rs.next()) {
				res = rs.getString(1);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public ResultSet executeSelect1(String query) {

		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(query);

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return rs;

	}

	/*
	 * Method : doUpdate() Returns : int Arguments : query - the query to be
	 * executed. Responsibility : to execute the update statements.
	 */
	public int doUpdate(String query) {
		int res = -1;
		try {
			res = stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (res);
	}

	/*
	 * Method : doInsert() Returns : void Arguments : query - the query to be
	 * executed. Responsibility : to execute the insert statements.
	 */
	public boolean doInsert(String query) {
		boolean res = false;
		try {
			stmt.execute(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (res);
	}

	/*
	 * Method : doSelect() Returns : ResultSet Arguments : query - the query to
	 * be executed. Responsibility : to execute the select statements.
	 */
	public ResultSet doSelect(String query) {
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (rs);
	}

	/*
	 * Method : doDelete() Returns : boolean Arguments : query - the query to be
	 * executed. Responsibility : to execute the delete statements.
	 */
	public boolean doDelete(String query) {
		boolean res = true;
		try {
			res = stmt.execute(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (res);
	}

	public int executeDML_Con(String query) {
		int result = 0;
		try {
			result = stmt.executeUpdate(query);
		} catch (SQLException c) {
			c.printStackTrace();
		}
		return result;

	}
	
	public int addDataToComments(String query) {
		int result = 0;
		try {
			result = stmt.executeUpdate(query);
		} catch (SQLException c) {
			c.printStackTrace();
		}
		return result;

	}

	@SuppressWarnings("finally")
	public int executeCheckUser(String query1) {
		int res = 0;
		try {
			res = stmt.executeUpdate(query1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			return res;
		}
	}

	public String executeDispConId(String query) {
		ResultSet rs = null;
		String s2 = null;
		try {
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				String s = rs.getString(1);
				s2 = s;
			}
		} catch (SQLException e) {

		}
		return s2;

	}

	public int executeDMLDelete(String query) {
		int result = 0;
		try {
			result = stmt.executeUpdate(query);

		} catch (SQLException c) {
			c.printStackTrace();
		}
		return result;

	}

	@SuppressWarnings("finally")
	public int executeDMLUpdate(String query) {
		int result = 0;
		try {
			result = stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			return result;
		}
	}

	public int getNextValue() {
		String query = "select comm_sequence.nextval from dual";
		ResultSet rs;
		int sequenceValue = 0;
		try {
			rs = stmt.executeQuery(query);

			while (rs.next()) {
				sequenceValue = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sequenceValue;
	}
}